package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import constants.Constants;
import domain.cull.SeasonalityCull;

public class CullDao {
	FileDao fileDao = new FileDao();
	
	String dbUrl = Constants.DB_URL;
	String dbUsername = Constants.DB_USERNAME;
	String dbPassword = Constants.DB_PASSWORD;
	
	public SeasonalityCull getSeasonality(int fileNo){
		SeasonalityCull result = new SeasonalityCull();
		String query = "select Abs_Adjustment, Pct_Adjustment, Seasonality, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec from dbo.DVIPV_CULL_SEASONALITY where File_No = "+fileNo;
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				result.setAbsAdjustment(rs.getDouble("Abs_Adjustment"));
				result.setPctAdjustment(rs.getDouble("Pct_Adjustment"));
				result.setOverallSeasonality(rs.getDouble("Seasonality"));
				
				for (int i=1;i<=12;i++){
					result.getMonthlySeasonality()[i] = rs.getDouble(Constants.MONTHS[i]);
				}
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		return result;
	}
	
	public double[] getCullRiskLact(int fileNo){
		double[] result = new double[Constants.LACT+1];
		String query = "select Lact1, Lact2, Lact3, Lact4, Lact5, Lact6, Lact7, Lact8, Lact9, Lact10, Lact11, Lact12 from dbo.DVIPV_CULL_RISK_LACT where File_No = "+fileNo;
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				for(int i=1;i<=Constants.LACT;i++){
					result[i] = rs.getDouble("Lact"+i);
				}
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public double[] getCullRiskMim(int fileNo){
		double[] result = new double[Constants.MIM+1];
		String query = "select MIM1, MIM2, MIM3, MIM4, MIM5, MIM6, MIM7, MIM8, MIM9, MIM10, MIM11, MIM12, MIM13, MIM14, MIM15, MIM16, MIM17, MIM18, MIM19, MIM20, MIM21, MIM22, MIM23, MIM24 from dbo.DVIPV_CULL_RISK_MIM where File_No = "+fileNo;
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				for(int i=1;i<=Constants.MIM;i++){
					result[i] = rs.getDouble("MIM"+i);
				}
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public boolean updateSeasonality(int fileNo, SeasonalityCull obj){
		boolean result = false;
		String query = "update dbo.DVIP_IN_CULL_SEASONALITY set Abs_Adjustment = ?, Pct_Adjustment = ?, Seasonality = ?, Jan = ?, Feb = ?, Mar = ?, Apr = ?, May = ?, Jun = ?, Jul = ?, Aug = ?, Sep = ?, Oct = ?, Nov = ?, Dec = ? where file_no = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			int month = 1;
			pStmt.setDouble(colIndex++, obj.getAbsAdjustment());
			pStmt.setDouble(colIndex++, obj.getPctAdjustment());
			pStmt.setDouble(colIndex++, obj.getOverallSeasonality());
			
			pStmt.setDouble(colIndex++, obj.getMonthlySeasonality()[month++]);
			pStmt.setDouble(colIndex++, obj.getMonthlySeasonality()[month++]);
			pStmt.setDouble(colIndex++, obj.getMonthlySeasonality()[month++]);
			
			pStmt.setDouble(colIndex++, obj.getMonthlySeasonality()[month++]);
			pStmt.setDouble(colIndex++, obj.getMonthlySeasonality()[month++]);
			pStmt.setDouble(colIndex++, obj.getMonthlySeasonality()[month++]);
			
			pStmt.setDouble(colIndex++, obj.getMonthlySeasonality()[month++]);
			pStmt.setDouble(colIndex++, obj.getMonthlySeasonality()[month++]);
			pStmt.setDouble(colIndex++, obj.getMonthlySeasonality()[month++]);
			
			pStmt.setDouble(colIndex++, obj.getMonthlySeasonality()[month++]);
			pStmt.setDouble(colIndex++, obj.getMonthlySeasonality()[month++]);
			pStmt.setDouble(colIndex++, obj.getMonthlySeasonality()[month++]);
			
			pStmt.setDouble(colIndex++, fileNo);
			
			int res = pStmt.executeUpdate();
			if(res>0){
				result = true;
			}
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	} 
	
	public boolean updateCullRiskLact(int fileNo, double[] obj){
		boolean result = false;
		String query = "update dbo.DVIP_IN_CULL_RISK_LACT set Lact1 = ?, Lact2 = ?, Lact3 = ?, Lact4 = ?, Lact5 = ?, Lact6 = ?, Lact7 = ?, Lact8 = ?, Lact9 = ?, Lact10 = ?, Lact11 = ?, Lact12 = ? where file_no = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			int lact = 1;
			pStmt.setDouble(colIndex++, obj[lact++]);
			pStmt.setDouble(colIndex++, obj[lact++]);
			pStmt.setDouble(colIndex++, obj[lact++]);
			
			pStmt.setDouble(colIndex++, obj[lact++]);
			pStmt.setDouble(colIndex++, obj[lact++]);
			pStmt.setDouble(colIndex++, obj[lact++]);
			
			pStmt.setDouble(colIndex++, obj[lact++]);
			pStmt.setDouble(colIndex++, obj[lact++]);
			pStmt.setDouble(colIndex++, obj[lact++]);
			
			pStmt.setDouble(colIndex++, obj[lact++]);
			pStmt.setDouble(colIndex++, obj[lact++]);
			pStmt.setDouble(colIndex++, obj[lact++]);
			
			pStmt.setDouble(colIndex++, fileNo);
			
			int res = pStmt.executeUpdate();
			if(res>0){
				result = true;
			}
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public boolean updateCullRiskMim(int fileNo, double[] obj){
		boolean result = false;
		String query = "update dbo.DVIP_IN_CULL_RISK_MIM set MIM1 = ?, MIM2 = ?, MIM3 = ?, MIM4 = ?, MIM5 = ?, MIM6 = ?, MIM7 = ?, MIM8 = ?, MIM9 = ?, MIM10 = ?, MIM11 = ?, MIM12 = ?, MIM13 = ?, MIM14 = ?, MIM15 = ?, MIM16 = ?, MIM17 = ?, MIM18 = ?, MIM19 = ?, MIM20 = ?, MIM21 = ?, MIM22 = ?, MIM23 = ?, MIM24 = ? where file_no = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			int mim = 1;
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			pStmt.setDouble(colIndex++, obj[mim++]);
			
			pStmt.setDouble(colIndex++, fileNo);
			
			int res = pStmt.executeUpdate();
			if(res>0){
				result = true;
			}
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
}
